﻿'參考>>COM>>Microsoft Office 12.0 Access database engine Object Library
'20130222
Public Class readAccess
    Implements DatabaseIF

    Private m_DBEngine As Microsoft.Office.Interop.Access.Dao.DBEngine
    Private m_Database As Microsoft.Office.Interop.Access.Dao.Database
    Private m_Recordset As Microsoft.Office.Interop.Access.Dao.Recordset

    Private m_TableName As String
    Private m_FilePath As String

    Sub New(ByVal FilePath As String, ByVal TableName As String)
   m_FilePath = FilePath
        m_TableName = TableName
        m_DBEngine = New Microsoft.Office.Interop.Access.Dao.DBEngine
        m_Database = m_DBEngine.OpenDatabase(m_FilePath)

        m_Recordset = m_Database.OpenRecordset(m_TableName)
        ' test_edit()

    End Sub

    Public Sub close() Implements DatabaseIF.close
        m_Recordset.Close()
        m_Database.Close()

    End Sub
#Region "test"
    Sub test_view()
        Dim DBEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
        Dim Database As Microsoft.Office.Interop.Access.Dao.Database

        Database = DBEngine.OpenDatabase("ProjectDatabase.accdb")
        'Database = DBEngine.CreateDatabase("ProjectDatabase.accdb", )

        For index As Integer = 0 To Database.TableDefs.Count - 1
            Console.WriteLine(Database.TableDefs(index).Name)
        Next
        Console.WriteLine()
        For index As Integer = 0 To Database.TableDefs.Count - 1
            Console.WriteLine(Database.TableDefs(index).Properties.ToString)
        Next
        Console.WriteLine()
        'TableDefs 資料表
        For index As Integer = 0 To Database.TableDefs("維護軟體點位表").Properties.Count - 1
            Console.WriteLine(Database.TableDefs("維護軟體點位表").Properties(index).Name)
        Next

        Console.WriteLine()
        For index As Integer = 0 To Database.TableDefs("維護軟體點位表").Fields.Count - 1
            Console.WriteLine(Database.TableDefs("維護軟體點位表").Fields(index).Name)
        Next
        Console.WriteLine()


        Console.WriteLine(Database.TableDefs("維護軟體點位表").Indexes.Count.ToString)
        Console.WriteLine(Database.TableDefs("維護軟體點位表").RecordCount.ToString)
        'For idnex As Integer = 0 To Database.TableDefs("維護軟體點位表").RecordCount - 1
        '    Console.WriteLine(Database.TableDefs("維護軟體點位表")("工務段").ForeignName)
        'Next
        Console.WriteLine()
        'QueryDefs 查詢
        For index As Integer = 0 To Database.QueryDefs.Count - 1
            Console.WriteLine(Database.QueryDefs(index).Name.ToString)
        Next
        'Relations ?
        Console.WriteLine()
        For index As Integer = 0 To Database.Relations.Count - 1
            Console.WriteLine(Database.Relations(index).Name.ToString)
        Next
        Console.WriteLine()
        'Containers
        For index As Integer = 0 To Database.Containers.Count - 1
            Console.WriteLine(Database.Containers(index).Name.ToString)
        Next
        Console.WriteLine()
        'Recordsets
        For index As Integer = 0 To Database.Recordsets.Count - 1
            Console.WriteLine(Database.Recordsets(index).Name.ToString)
        Next
        Console.WriteLine()


        Console.WriteLine(Database("維護軟體點位表").RecordCount)
        Console.WriteLine(Database("維護軟體點位表")("工務段").Name)
        Console.WriteLine(Database("維護軟體點位表").Fields.Count.ToString)
        Console.WriteLine(Database("維護軟體點位表").Fields("工務段").Properties.Count.ToString)
        'For index As Integer = 0 To Database("維護軟體點位表").Fields("工務段").Properties.Count - 1
        '    Console.WriteLine(Database("維護軟體點位表").Fields("工務段").
        'Next
        'Console.WriteLine(Database.QueryDefs("維護軟體點位表")(2).Value.ToString)
    End Sub
    Sub test_edit()
        Dim DBEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
        Dim Database As Microsoft.Office.Interop.Access.Dao.Database

        Database = DBEngine.OpenDatabase("ProjectDatabase.accdb")
        Dim Recordset As Microsoft.Office.Interop.Access.Dao.Recordset
        Recordset = Database.OpenRecordset("維護軟體點位表")
        Recordset.MoveLast()
        Recordset.MoveFirst()

        Console.WriteLine("RecordCount " + Recordset.RecordCount.ToString)
        'Dim Recordset_Fields As Microsoft.Office.Interop.Access.Dao.Recordset
        'Recordset_Fields = Recordset.Fields("工務段").Value
        'Recordset_Fields. 
        Recordset.MoveFirst()
        For index As Integer = 0 To Recordset.RecordCount - 1
            Console.WriteLine(Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
            Recordset.MoveNext()

        Next
        Recordset.MoveFirst()
        While Not Recordset.EOF
            Console.WriteLine(Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
            Recordset.MoveNext()
        End While

        Recordset.MoveFirst()
        Recordset.MovePrevious()
        'Dim varBookmark As Object = Recordset.Bookmark
        'Console.WriteLine(varBookmark.ToString)
        'Recordset.MoveLast()



        'Console.WriteLine(Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
        'varBookmark = Recordset.Bookmark
        'Console.WriteLine(varBookmark.ToString)
        'Recordset = Database.OpenRecordset("維護軟體點位表")
        Recordset.MoveLast()
        Recordset.MovePrevious()
        Console.WriteLine(Recordset.Index + " " + Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
        Dim a As Object = Recordset.Bookmark
        Recordset.Move(-5)
        Console.WriteLine(Recordset.Index + " " + Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
        Recordset.Move(-15, a)
        Console.WriteLine(Recordset.Index + " " + Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
        Recordset.AddNew()
        Recordset.Fields("工務段").Value = "無此段99"

        Recordset.Update()
        Console.WriteLine(Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
        Recordset.MoveFirst()
        Dim i As Integer = 1

        While Not Recordset.EOF

            Recordset.Edit()
            Recordset.Fields("車道數").Value = i.ToString

            i = i + 1
            Recordset.Update()
            Recordset.MoveNext()
        End While
        Console.WriteLine("RecordCount " + Recordset.RecordCount.ToString)
        Recordset.Close()
        Recordset = Database.OpenRecordset("維護軟體點位表")
        Recordset.MoveLast()
        Recordset.MoveFirst()

        Console.WriteLine("RecordCount " + Recordset.RecordCount.ToString)
        'Recordset.Move(22, "識別碼")
        'Console.WriteLine(Recordset.Fields("識別碼").Value.ToString + "    " + Recordset.Fields("工務段").Value.ToString)
        Dim FindString As String = "斗南段"
        'FindMatch(Database.OpenRecordset("SELECT 工務段 FROM 維護軟體點位表 ORDER BY 工務段"), "工務段 = '新化段'")
        Console.WriteLine("test FindMatch")
        FindMatch(Database.OpenRecordset("SELECT * FROM 維護軟體點位表 ORDER BY 工務段"), "工務段 = '" & FindString & "'")
        Console.WriteLine("test SeekMatch")
        Dim seekNumber As Integer = 2

        'x
        'SeekMatch(Database.OpenRecordset("維護軟體點位表"), seekNumber)
    End Sub

    Sub SeekMatch(ByVal rstTemp As Microsoft.Office.Interop.Access.Dao.Recordset, ByVal intSeek As Integer)

        Dim varBookmark As Object
        Dim strMessage As String
        Console.WriteLine("RecordCount " + rstTemp.RecordCount.ToString)
        With rstTemp
            ' Store current record location.
            varBookmark = .Bookmark
            .Seek("=", intSeek)

            ' If Seek method fails, notify user and return to the 
            ' last current record.
            If .NoMatch Then
                strMessage = _
                   "Not found! Returning to current record." & _
                   vbCr & vbCr & "NoMatch = " & .NoMatch
                Console.WriteLine(strMessage)
                .Bookmark = varBookmark
            Else
                Console.WriteLine("Match")
            End If

        End With

    End Sub
    Sub FindMatch(ByVal rstTemp As Microsoft.Office.Interop.Access.Dao.Recordset, ByVal strFind As String)

        Dim varBookmark As Object
        Dim strMessage As String
        Console.WriteLine("RecordCount " + rstTemp.RecordCount.ToString)
        With rstTemp
            ' Store current record location.
            varBookmark = .Bookmark
            .FindFirst(strFind)

            ' If Find method fails, notify user and return to the 
            ' last current record.
            If .NoMatch Then
                strMessage = _
                   "Not found! Returning to current record." & _
                   vbCr & vbCr & "NoMatch = " & .NoMatch
                Console.WriteLine(strMessage)
                .Bookmark = varBookmark
            Else
                Console.WriteLine("Match")
            End If

        End With

    End Sub

#End Region

    Function getRecordCount(ByVal rstTemp As Microsoft.Office.Interop.Access.Dao.Recordset) As Long
        Dim count As Long = 0
        Try
            If Not rstTemp.BOF Then
                rstTemp.MoveFirst()
            End If

            Console.WriteLine("count " + rstTemp.RecordCount.ToString)
            While Not rstTemp.EOF
                count = count + 1
                rstTemp.MoveNext()
            End While
        Catch ex As Exception
            Console.WriteLine("readAccess.getRecordCount " + ex.ToString)
        End Try
        'If count = 0 Then
        '    Console.WriteLine("count 0")
        'End If
        Return count
    End Function

    Function getRecordCount() As Long
        Dim count As Long = 0
        Dim t_Recordset As Microsoft.Office.Interop.Access.Dao.Recordset = m_Database.OpenRecordset(m_TableName)
        Try
            t_Recordset.MoveFirst()
            While Not t_Recordset.EOF
                count = count + 1
                t_Recordset.MoveNext()
                ' Console.WriteLine(count.ToString)
            End While
        Catch ex As Exception
            count = 0
        End Try
        t_Recordset.Close()
        Return count
    End Function
    ReadOnly Property Recordset As Microsoft.Office.Interop.Access.Dao.Recordset
        Get
            Return m_Recordset
        End Get
    End Property


    Public Function useSQL(ByVal SQLtext As String) As System.Data.DataTable Implements DatabaseIF.useSelect

        'Database.OpenRecordset("SELECT * FROM 維護軟體點位表 ORDER BY 工務段")
        Dim table As System.Data.DataTable
        '  Dim sql_Recordset As Microsoft.Office.Interop.Access.Dao.Recordset = m_Database.OpenRecordset(SQLtext)
        Dim tempRecordset As Microsoft.Office.Interop.Access.Dao.Recordset = m_Database.OpenRecordset(SQLtext)
        table = New System.Data.DataTable(tempRecordset.Name)


        '設定欄位

        For index As Integer = 0 To tempRecordset.Fields.Count - 1

            table.Columns.Add(tempRecordset.Fields(index).Name.ToString())
        Next

        If Not getRecordCount(tempRecordset) = 0 Then
            '增加資料行數
            For index As Integer = 0 To getRecordCount(tempRecordset) - 1

                table.Rows.Add()
            Next
            '填資料
            Dim TableRow As DataRow() = table.Select(Nothing, Nothing, DataViewRowState.CurrentRows)
            tempRecordset.MoveFirst()
            Dim RecordsetCount As Integer = 0
            While Not tempRecordset.EOF

                For index2 As Integer = 0 To TableRow(RecordsetCount).Table.Columns.Count - 1
                    TableRow(RecordsetCount)(index2) = tempRecordset.Fields(index2).Value.ToString
                Next
                ' Console.WriteLine(table.Columns(3).ToString)
                'Console.WriteLine(TableRow(RecordsetCount)(3).ToString)
                RecordsetCount = RecordsetCount + 1
                tempRecordset.MoveNext()
            End While
        End If



        'Console.WriteLine(table.Rows.Count.ToString)
        Return table
    End Function
    Private Function RecordsetToDataTable(ByVal tempRecordset As Microsoft.Office.Interop.Access.Dao.Recordset) As System.Data.DataTable
        Dim table As System.Data.DataTable
        table = New System.Data.DataTable(tempRecordset.Name)
        '設定欄位

        For index As Integer = 0 To tempRecordset.Fields.Count - 1

            table.Columns.Add(tempRecordset.Fields(index).Name.ToString())
        Next

        '增加資料行數
        For index As Integer = 0 To getRecordCount(tempRecordset) - 1

            table.Rows.Add()
        Next
        '填資料
        Dim TableRow As DataRow() = table.Select(Nothing, Nothing, DataViewRowState.CurrentRows)

        tempRecordset.MoveFirst()
        Dim RecordsetCount As Integer = 0
        While Not tempRecordset.EOF

            For index2 As Integer = 0 To TableRow(RecordsetCount).Table.Columns.Count - 1


                TableRow(RecordsetCount)(index2) = tempRecordset.Fields(index2).Value.ToString

            Next

            ' Console.WriteLine(table.Columns(3).ToString)
            'Console.WriteLine(TableRow(RecordsetCount)(3).ToString)

            RecordsetCount = RecordsetCount + 1
            tempRecordset.MoveNext()
        End While

        ' Console.WriteLine(table.Rows.Count.ToString)
        Return table
    End Function

    Public Function readTable() As System.Data.DataTable Implements DatabaseIF.readTable
        Return RecordsetToDataTable(Me.m_Recordset)
    End Function


    Public Sub writeTable(ByVal DataTable As System.Data.DataTable) Implements DatabaseIF.writeTable
        Try
            'm_Recordset.EditMode
            If m_Recordset.RecordCount > 0 Then
                m_Recordset.MoveFirst()
                '   m_Recordset.MoveLast()
            End If

            Dim TableRow As DataRow() = DataTable.Select(Nothing, Nothing, DataViewRowState.CurrentRows)

            For indexY As Integer = 0 To DataTable.Rows.Count - 1
                m_Recordset.AddNew()
                For indexX As Integer = 1 To DataTable.Columns.Count - 1

                    m_Recordset.Fields(indexX).Value = TableRow(indexY)(indexX)

                Next
                'm_Recordset.Connection
                m_Recordset.Update()
                ' Console.WriteLine(indexY.ToString)
            Next
            Console.WriteLine("real Count" + Me.getRecordCount.ToString)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


    End Sub
 


    Public Function useUpdata(ByVal SQLtext As String, ByVal Value() As Object) As Integer Implements DatabaseIF.useUpdata
        Return -1
    End Function
End Class

